
package net.swa.dewei.service.impl;

import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Service;
import net.swa.dewei.beans.entity.FuwuZhan;
import net.swa.dewei.beans.vo.Statis;
import net.swa.dewei.beans.vo.StatisMendian;
import net.swa.dewei.beans.vo.StatisPlusMendian;
import net.swa.dewei.service.StatisService;
import net.swa.system.dao.HibernateDaoSupport;
import net.swa.util.BuzConst;
import net.swa.util.JsonResult;
import net.swa.util.io.ExcelUtil;
import net.swa.util.io.enums.MsOfficeVersion;

@Service("statisService")
public class StatisServiceImpl extends HibernateDaoSupport implements StatisService
{

    /**将格式转换为"yyyy-MM-dd HH:mm:ss"
     * @param sdate2
     * @param b true:yyyy-MM-dd 00:00:00;false:yyyy-MM-dd 23:59:59
     * @return
     */
    private String getFormatDate(String date , boolean b)
    {
        if (!StringUtils.isBlank(date))
        {
            if (b)
            {
                return date + " 00:00:00";
            }
            else
            {
                return date + " 23:59:59";
            }
        }
        return null;
    }

    @Override
    public JsonResult<Statis> queryPage(String sdate , String edate , String name , int currentPage , int pageSize)
    {
        sdate = getFormatDate(sdate, true);
        edate = getFormatDate(edate, false);
        JsonResult<Statis> json = new JsonResult<Statis>();
        Session session = getCurrentSession();
        StringBuilder sql = new StringBuilder();
        StringBuilder totalsql = new StringBuilder();
        totalsql.append("select sum(usNum) as usNum ,sum(totalNum) as totalNum,SUM(sum0) sum0,SUM(sum1) sum1,SUM(sum2) sum2,SUM(sum3) sum3,SUM(sum4)sum4,SUM(sum5) sum5,sum(sum_near) sum_near,sum(sum_shm) as sum_shm,sum(sum_wwd) as sum_wwd,'总计' as `name`,'' as lxr,'' as address,null as fuwuzhanId from (");

        sql.append("select count(userId) as usNum ,sum(totalNum) as totalNum,SUM(sum0) sum0,SUM(sum1) sum1,SUM(sum2) sum2,SUM(sum3) sum3,SUM(sum4)sum4,SUM(sum5) sum5,sum(sum_near) sum_near,sum(sum_shm) as sum_shm,sum(sum_wwd) as sum_wwd, `name`,lxr,address,fuwuzhanId  FROM(");
        sql.append("select userId ,sum(totalNum) as totalNum,SUM(sum0) sum0,SUM(sum1) sum1,SUM(sum2) sum2,SUM(sum3) sum3,SUM(sum4)sum4,SUM(sum5) sum5,sum(sum_near) sum_near,sum(sum_shm) as sum_shm,sum(sum_wwd) as sum_wwd, `name`,lxr,address,fuwuzhanId  FROM(");
        sql.append("select  userId,count(id) as totalNum,SUM(state0) sum0,SUM(state1) sum1,SUM(state2) sum2,SUM(state3) sum3,SUM(state4) sum4,SUM(state5) sum5,sum(fujin) sum_near,sum(shm) as sum_shm,sum(wwd) as sum_wwd,fuwuzhanId,`name`,lxr,address FROM V_STATIS_01 where 1=1 ");
        if (!StringUtils.isBlank(sdate))
        {
            sql.append(" and   crateDate>=:sdate ");
        }
        if (!StringUtils.isBlank(edate))
        {
            sql.append(" and   crateDate<=:edate ");
        }
        if (!StringUtils.isBlank(name))
        {
            sql.append(" and   name like :name ");
        }

        sql.append("GROUP BY `name`,lxr,address,fuwuzhanId,userId UNION ALL ");
        sql.append("select userId,count(id) as totalNum,SUM(state0) sum0,SUM(state1) sum1,SUM(state2) sum2,SUM(state3) sum3,SUM(state4) sum4,SUM(state5) sum5,sum(fujin) sum_near,sum(shm) as sum_shm,sum(wwd) as sum_wwd,fuwuzhanId,`name`,lxr,address FROM V_STATIS_02 where 1=1");
        if (!StringUtils.isBlank(sdate))
        {
            sql.append(" and   crateDate>=:sdate2 ");
        }
        if (!StringUtils.isBlank(edate))
        {
            sql.append(" and   crateDate<=:edate2 ");
        }
        if (!StringUtils.isBlank(name))
        {
            sql.append(" and   name like :name2 ");
        }

        sql.append("GROUP BY `name`,lxr,address,fuwuzhanId,userId)rr  GROUP BY `name`,lxr,address,fuwuzhanId,userId) rrr  GROUP BY `name`,lxr,address,fuwuzhanId order  by fuwuzhanId ");

        totalsql.append(sql + ") as total ");
        System.out.println(sql.toString());
        SQLQuery ss = (SQLQuery) session.createSQLQuery(sql.toString()).addScalar("address", StringType.INSTANCE).addScalar("fuwuzhanId", StandardBasicTypes.LONG).addScalar("lxr", StandardBasicTypes.STRING).addScalar("name", StandardBasicTypes.STRING).addScalar("totalNum", StandardBasicTypes.INTEGER).addScalar("sum_near", StandardBasicTypes.INTEGER).addScalar("sum_shm", StandardBasicTypes.INTEGER).addScalar("sum_wwd", StandardBasicTypes.INTEGER).addScalar("sum0", StandardBasicTypes.INTEGER).addScalar("sum1",
                StandardBasicTypes.INTEGER).addScalar("usNum", StandardBasicTypes.INTEGER).addScalar("sum2", StandardBasicTypes.INTEGER).addScalar("sum3", StandardBasicTypes.INTEGER).addScalar("sum4", StandardBasicTypes.INTEGER).addScalar("sum5", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(Statis.class));

        if (!StringUtils.isBlank(sdate))
        {
            ss.setString("sdate", sdate);
            ss.setString("sdate2", sdate);
        }
        if (!StringUtils.isBlank(edate))
        {
            ss.setString("edate", edate);
            ss.setString("edate2", edate);
        }
        if (!StringUtils.isBlank(name))
        {
            ss.setString("name", "%" + name + "%");
            ss.setString("name2", "%" + name + "%");
        }

        if (pageSize > 0)
        {
            int from = (currentPage - 1) * pageSize;
            ss.setFirstResult(from);
            ss.setMaxResults(pageSize);
        }

        List<Statis> list = ss.list();
        json.setResult(list);
        ss = getCurrentSession().createSQLQuery("select count(*) as counNum from (" + sql + ") t").addScalar("counNum", StandardBasicTypes.INTEGER);

        if (!StringUtils.isBlank(sdate))
        {
            ss.setString("sdate", sdate);
            ss.setString("sdate2", sdate);
        }
        if (!StringUtils.isBlank(edate))
        {
            ss.setString("edate", edate);
            ss.setString("edate2", edate);
        }
        if (!StringUtils.isBlank(name))
        {
            ss.setString("name", "%" + name + "%");
            ss.setString("name2", "%" + name + "%");
        }
        if (pageSize > 0)
        {
            int totalCount = (Integer) ss.uniqueResult();
            int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
            json.setTotalSize(totalCount);
            json.setTotalPage(totalPage);
            json.setPageSize(pageSize);
            json.setCurrentPage(currentPage);
        }
        if  (currentPage < 2)
        {
            ss = (SQLQuery) session.createSQLQuery(totalsql.toString()).addScalar("address", StringType.INSTANCE).addScalar("fuwuzhanId", StandardBasicTypes.LONG).addScalar("lxr", StandardBasicTypes.STRING).addScalar("name", StandardBasicTypes.STRING).addScalar("totalNum", StandardBasicTypes.INTEGER).addScalar("sum_near", StandardBasicTypes.INTEGER).addScalar("sum_shm", StandardBasicTypes.INTEGER).addScalar("sum_wwd", StandardBasicTypes.INTEGER).addScalar("sum0", StandardBasicTypes.INTEGER).addScalar("sum1",
                    StandardBasicTypes.INTEGER).addScalar("usNum", StandardBasicTypes.INTEGER).addScalar("sum2", StandardBasicTypes.INTEGER).addScalar("sum3", StandardBasicTypes.INTEGER).addScalar("sum4", StandardBasicTypes.INTEGER).addScalar("sum5", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(Statis.class));

            if (!StringUtils.isBlank(sdate))
            {
                ss.setString("sdate", sdate);
                ss.setString("sdate2", sdate);
            }
            if (!StringUtils.isBlank(edate))
            {
                ss.setString("edate", edate);
                ss.setString("edate2", edate);
            }
            if (!StringUtils.isBlank(name))
            {
                ss.setString("name", "%" + name + "%");
                ss.setString("name2", "%" + name + "%");
            }
            List<Statis> ttlist = ss.list();
            if (null != ttlist && ttlist.size() > 0)
            {
                ttlist.addAll(list);
                json.setResult(ttlist);
            }
        }
        
        return json;
    }

    @Override
    public HSSFWorkbook exportExcel(String sdate , String edate , String name)
    {
        String sheetname = "所有团队";
        if (!StringUtils.isBlank(name))
        {
            sheetname = name;
        }
        if (!StringUtils.isBlank(sdate) && !StringUtils.isBlank(edate))
        {
            sheetname += sdate + "~" + edate;
        }

        sdate = getFormatDate(sdate, true);
        edate = getFormatDate(edate, false);
        Session session = getCurrentSession();
        StringBuilder sql = new StringBuilder();
        sql.append("select count(userId) as usNum ,sum(totalNum) as totalNum,SUM(sum0) sum0,SUM(sum1) sum1,SUM(sum2) sum2,SUM(sum3) sum3,SUM(sum4)sum4,SUM(sum5) sum5,sum(sum_near) sum_near,sum(sum_shm) as sum_shm,sum(sum_wwd) as sum_wwd, `name`,lxr,address,fuwuzhanId  FROM(");
        sql.append("select userId ,sum(totalNum) as totalNum,SUM(sum0) sum0,SUM(sum1) sum1,SUM(sum2) sum2,SUM(sum3) sum3,SUM(sum4)sum4,SUM(sum5) sum5,sum(sum_near) sum_near,sum(sum_shm) as sum_shm,sum(sum_wwd) as sum_wwd, `name`,lxr,address,fuwuzhanId  FROM(");
        sql.append("select  userId,count(id) as totalNum,SUM(state0) sum0,SUM(state1) sum1,SUM(state2) sum2,SUM(state3) sum3,SUM(state4) sum4,SUM(state5) sum5,sum(fujin) sum_near,sum(shm) as sum_shm,sum(wwd) as sum_wwd,fuwuzhanId,`name`,lxr,address FROM V_STATIS_01 where 1=1 ");
        if (!StringUtils.isBlank(sdate))
        {
            sql.append(" and   crateDate>=:sdate ");
        }
        if (!StringUtils.isBlank(edate))
        {
            sql.append(" and   crateDate<=:edate ");
        }
        if (!StringUtils.isBlank(name))
        {
            sql.append(" and   name like :name ");
        }

        sql.append("GROUP BY `name`,lxr,address,fuwuzhanId,userId UNION ALL ");
        sql.append("select userId,count(id) as totalNum,SUM(state0) sum0,SUM(state1) sum1,SUM(state2) sum2,SUM(state3) sum3,SUM(state4) sum4,SUM(state5) sum5,sum(fujin) sum_near,sum(shm) as sum_shm,sum(wwd) as sum_wwd,fuwuzhanId,`name`,lxr,address FROM V_STATIS_02 where 1=1");
        if (!StringUtils.isBlank(sdate))
        {
            sql.append(" and   crateDate>=:sdate2 ");
        }
        if (!StringUtils.isBlank(edate))
        {
            sql.append(" and   crateDate<=:edate2 ");
        }
        if (!StringUtils.isBlank(name))
        {
            sql.append(" and   name like :name2 ");
        }

        sql.append("GROUP BY `name`,lxr,address,fuwuzhanId,userId)rr  GROUP BY `name`,lxr,address,fuwuzhanId,userId) rrr  GROUP BY `name`,lxr,address,fuwuzhanId");

        System.out.println(sql.toString());
        SQLQuery ss = (SQLQuery) session.createSQLQuery(sql.toString()).addScalar("address", StringType.INSTANCE).addScalar("fuwuzhanId", StandardBasicTypes.LONG).addScalar("lxr", StandardBasicTypes.STRING).addScalar("name", StandardBasicTypes.STRING).addScalar("totalNum", StandardBasicTypes.INTEGER).addScalar("sum_near", StandardBasicTypes.INTEGER).addScalar("sum_shm", StandardBasicTypes.INTEGER).addScalar("sum_wwd", StandardBasicTypes.INTEGER).addScalar("sum0", StandardBasicTypes.INTEGER).addScalar("sum1",
                StandardBasicTypes.INTEGER).addScalar("usNum", StandardBasicTypes.INTEGER).addScalar("sum2", StandardBasicTypes.INTEGER).addScalar("sum3", StandardBasicTypes.INTEGER).addScalar("sum4", StandardBasicTypes.INTEGER).addScalar("sum5", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(Statis.class));

        if (!StringUtils.isBlank(sdate))
        {
            ss.setString("sdate", sdate);
            ss.setString("sdate2", sdate);
        }
        if (!StringUtils.isBlank(edate))
        {
            ss.setString("edate", edate);
            ss.setString("edate2", edate);
        }
        if (!StringUtils.isBlank(name))
        {
            ss.setString("name", "%" + name + "%");
            ss.setString("name2", "%" + name + "%");
        }

        List<Statis> list = ss.list();
        HSSFWorkbook book = null;
        if (null != list && list.size() > 0)
        {
            // 要从对象中获取的属性名
            String fieldNames[] = { "fuwuzhanId", "name", "usNum", "totalNum", "sum_near", "sum_shm", "sum0", "sum1", "sum2", "sum3", "sum4", "address", "lxr" };
            String titleNames[] = { "团队编号", "团队名称", "用户数", "订单总数", "附件门店订单", "上门服务订单", "未处理订单", "完成订单", "超期订单", "指派订单", "作废订单", "团队地址", "联系人" };
            try
            {
                book = ExcelUtil.writeToExcelFile(list, fieldNames, titleNames, 0, 0, "filename", sheetname, true, null, MsOfficeVersion.Office2003, null);
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
        return book;
    }

    @Override
    public JsonResult<StatisMendian> queryStaticPage(Long fuwuzhanId , String sdate , String edate , String name , String changeType , int currentPage , int pageSize)
    {
        sdate = getFormatDate(sdate, true);
        edate = getFormatDate(edate, false);
        JsonResult<StatisMendian> json = new JsonResult<StatisMendian>();
        if (null != fuwuzhanId)
        {
            Session session = getCurrentSession();

            FuwuZhan fuwuzhan = (FuwuZhan) session.get(FuwuZhan.class, fuwuzhanId);

            StringBuilder sql = new StringBuilder();
            StringBuilder countsql = new StringBuilder();
            StringBuilder totalsql = new StringBuilder();
            Query query = null;

            totalsql.append("SELECT sum(usNum) AS usNum,sum(totalNum) AS totalNum,SUM(sum0) AS sum0,  SUM(sum1) AS sum1,SUM(sum2) AS sum2,SUM(sum3) AS sum3,SUM(sum4) AS sum4,SUM(sum5) AS sum5,sum(sum_near) AS sum_near,sum(sum_shm) AS sum_shm,sum(sum_wwd) AS sum_wwd,NUll as mendianId,'总计' AS `name`,null as lxr,NULL as address  FROM(");
            sql.append("SELECT count(userId) AS usNum,sum(totalNum) AS totalNum,SUM(sum0) AS sum0,  SUM(sum1) AS sum1,SUM(sum2) AS sum2,SUM(sum3) AS sum3,SUM(sum4) AS sum4,SUM(sum5) AS sum5,sum(sum_near) AS sum_near,sum(sum_shm) AS sum_shm,sum(sum_wwd) AS sum_wwd,mendianId,`name`,lxr,address  FROM(");
            sql.append("SELECT userId,count(id) AS totalNum,mendianId,`name`, address,lxr,sum(state0) sum0,sum(state1) sum1,sum(state2) sum2,sum(state3) sum3,sum(state4) sum4,sum(state5) sum5,sum(fujin) sum_near,sum(shm) sum_shm,sum(wwd) sum_wwd  FROM V_FWZ_STATIS_01 ");
            sql.append(" WHERE fuwuzhanId=:fuwuzhanId ");
            if (!StringUtils.isBlank(sdate))
            {
                sql.append(" and crateDate>=:sdate ");
            }
            if (!StringUtils.isBlank(edate))
            {
                sql.append(" and crateDate<=:edate ");
            }
            if (!StringUtils.isBlank(changeType))
            {
                sql.append(" and changeType=:changeType ");
            }

            if (!StringUtils.isBlank(name))
            {
                sql.append(" and name like :name");
            }
            sql.append(" GROUP BY address,lxr,`name`,mendianId,userId ");

            if (StringUtils.isBlank(name))
            {
                sql.append(" UNION SELECT userId,count(id) AS totalNum,mendianId,`name`, address,lxr,sum(state0) sum0,sum(state1) sum1,sum(state2) sum2,sum(state3) sum3,sum(state4) sum4,sum(state5) sum5,sum(fujin) sum_near,sum(shm) sum_shm,sum(wwd) sum_wwd  FROM V_FWZ_STATIS_02  ");
                sql.append(" WHERE 1=1 ");
                if (!StringUtils.isBlank(fuwuzhan.getProvince()) && (!"请选择".equals(fuwuzhan.getProvince())))
                {
                    sql.append(" and  province like :province ");
                    if (!StringUtils.isBlank(fuwuzhan.getCity()) && (!"请选择".equals(fuwuzhan.getCity())))
                    {
                        sql.append(" and city like :city ");
                        if (!StringUtils.isBlank(fuwuzhan.getTown()) && (!"请选择".equals(fuwuzhan.getTown())))
                        {
                            sql.append(" and town like :town ");
                        }
                    }
                }
                if (!StringUtils.isBlank(sdate))
                {
                    sql.append(" and crateDate>=:sdate2 ");
                }
                if (!StringUtils.isBlank(edate))
                {
                    sql.append(" and crateDate<=:edate2 ");
                }
                if (!StringUtils.isBlank(name))
                {
                    sql.append(" and name like :name2");
                }
                if (!StringUtils.isBlank(changeType))
                {
                    sql.append(" and changeType=:changeType2 ");
                }
                sql.append(" GROUP BY address,lxr,`name`,mendianId,userId");
                sql.append(" UNION  SELECT userId,count(id) AS totalNum,mendianId,`name`, address,lxr,sum(state0) sum0,sum(state1) sum1,sum(state2) sum2,sum(state3) sum3,sum(state4) sum4,sum(state5) sum5,sum(fujin) sum_near,sum(shm) sum_shm,sum(wwd) sum_wwd  FROM V_FWZ_STATIS_01_s1 ");

                sql.append(" WHERE fuwuzhanId=:fuwuzhanId2 ");

                sql.append(" GROUP BY address,lxr,`name`,mendianId,userId ");
            }

            sql.append(") r GROUP BY  address,lxr,`name`,mendianId    order  by mendianId");

            countsql.append("select count(*) as counNum from (" + sql + ") as result ");

            totalsql.append(sql + " ) too ");//总计的统计

            query = session.createSQLQuery(sql.toString()).addScalar("address", StringType.INSTANCE).addScalar("mendianId", StandardBasicTypes.LONG).addScalar("lxr", StandardBasicTypes.STRING).addScalar("name", StandardBasicTypes.STRING).addScalar("totalNum", StandardBasicTypes.INTEGER).addScalar("sum_near", StandardBasicTypes.INTEGER).addScalar("sum_shm", StandardBasicTypes.INTEGER).addScalar("sum_wwd", StandardBasicTypes.INTEGER).addScalar("sum0", StandardBasicTypes.INTEGER).addScalar("sum1",
                    StandardBasicTypes.INTEGER).addScalar("usNum", StandardBasicTypes.INTEGER).addScalar("sum2", StandardBasicTypes.INTEGER).addScalar("sum3", StandardBasicTypes.INTEGER).addScalar("sum4", StandardBasicTypes.INTEGER).addScalar("sum5", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(StatisMendian.class));

            query.setMaxResults(pageSize);
            query.setLong("fuwuzhanId", fuwuzhanId);
            if (!StringUtils.isBlank(sdate))
            {
                query.setString("sdate", sdate);
            }
            if (!StringUtils.isBlank(edate))
            {
                query.setString("edate", edate);
            }
            if (!StringUtils.isBlank(changeType))
            {
                query.setString("changeType", changeType);
            }
            if (!StringUtils.isBlank(name))
            {
                query.setString("name", "%" + name + "%");
            }
            if (StringUtils.isBlank(name))
            {
                if (!StringUtils.isBlank(fuwuzhan.getProvince()) && (!"请选择".equals(fuwuzhan.getProvince())))
                {
                    query.setString("province", fuwuzhan.getProvince() + "%");
                    if (!StringUtils.isBlank(fuwuzhan.getCity()) && (!"请选择".equals(fuwuzhan.getCity())))
                    {
                        query.setString("city", fuwuzhan.getCity().replace("市", "") + "%");
                        if (!StringUtils.isBlank(fuwuzhan.getTown()) && (!"请选择".equals(fuwuzhan.getTown())))
                        {
                            query.setString("town", fuwuzhan.getTown() + "%");
                        }
                    }
                }
                query.setLong("fuwuzhanId2", fuwuzhanId);
                if (!StringUtils.isBlank(sdate))
                {
                    query.setString("sdate2", sdate);
                }
                if (!StringUtils.isBlank(edate))
                {
                    query.setString("edate2", edate);
                }
                if (!StringUtils.isBlank(changeType))
                {
                    query.setString("changeType2", changeType);
                }
                if (!StringUtils.isBlank(name))
                {
                    query.setString("name2", "%" + name + "%");
                }
            }

            if (pageSize > 0)
            {
                int from = (currentPage - 1) * pageSize;
                query.setFirstResult(from);
                query.setMaxResults(pageSize);

            }
            List<StatisMendian> list = query.list();
            query = session.createSQLQuery(countsql.toString()).addScalar("counNum", StandardBasicTypes.INTEGER);
            query.setLong("fuwuzhanId", fuwuzhanId);
            if (!StringUtils.isBlank(sdate))
            {
                query.setString("sdate", sdate);
            }
            if (!StringUtils.isBlank(edate))
            {
                query.setString("edate", edate);
            }
            if (!StringUtils.isBlank(changeType))
            {
                query.setString("changeType", changeType);
            }

            if (!StringUtils.isBlank(name))
            {
                query.setString("name", "%" + name + "%");
            }
            if (StringUtils.isBlank(name))
            {
                if (!StringUtils.isBlank(fuwuzhan.getProvince()) && (!"请选择".equals(fuwuzhan.getProvince())))
                {
                    query.setString("province", fuwuzhan.getProvince() + "%");
                    if (!StringUtils.isBlank(fuwuzhan.getCity()) && (!"请选择".equals(fuwuzhan.getCity())))
                    {
                        query.setString("city", fuwuzhan.getCity().replace("市", "") + "%");
                        if (!StringUtils.isBlank(fuwuzhan.getTown()) && (!"请选择".equals(fuwuzhan.getTown())))
                        {
                            query.setString("town", fuwuzhan.getTown() + "%");
                        }
                    }
                }
                query.setLong("fuwuzhanId2", fuwuzhanId);
                if (!StringUtils.isBlank(sdate))
                {
                    query.setString("sdate2", sdate);
                }
                if (!StringUtils.isBlank(edate))
                {
                    query.setString("edate2", edate);
                }
                if (!StringUtils.isBlank(changeType))
                {
                    query.setString("changeType2", changeType);
                }
                if (!StringUtils.isBlank(name))
                {
                    query.setString("name2", "%" + name + "%");
                }
            }
            int totalCount = (Integer) query.uniqueResult();
            json.setTotalSize(totalCount);
            json.setPageSize(pageSize);
            json.setResult(list);
            if (currentPage == 1)
            {
                query = session.createSQLQuery(totalsql.toString()).addScalar("address", StringType.INSTANCE).addScalar("mendianId", StandardBasicTypes.LONG).addScalar("lxr", StandardBasicTypes.STRING).addScalar("name", StandardBasicTypes.STRING).addScalar("totalNum", StandardBasicTypes.INTEGER).addScalar("sum_near", StandardBasicTypes.INTEGER).addScalar("sum_shm", StandardBasicTypes.INTEGER).addScalar("sum_wwd", StandardBasicTypes.INTEGER).addScalar("sum0", StandardBasicTypes.INTEGER).addScalar("sum1",
                        StandardBasicTypes.INTEGER).addScalar("usNum", StandardBasicTypes.INTEGER).addScalar("sum2", StandardBasicTypes.INTEGER).addScalar("sum3", StandardBasicTypes.INTEGER).addScalar("sum4", StandardBasicTypes.INTEGER).addScalar("sum5", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(StatisMendian.class));

                query.setLong("fuwuzhanId", fuwuzhanId);
                if (!StringUtils.isBlank(sdate))
                {
                    query.setString("sdate", sdate);
                }
                if (!StringUtils.isBlank(edate))
                {
                    query.setString("edate", edate);
                }
                if (!StringUtils.isBlank(changeType))
                {
                    query.setString("changeType", changeType);
                }

                if (!StringUtils.isBlank(name))
                {
                    query.setString("name", "%" + name + "%");
                }
                if (StringUtils.isBlank(name))
                {
                    if (!StringUtils.isBlank(fuwuzhan.getProvince()) && (!"请选择".equals(fuwuzhan.getProvince())))
                    {
                        query.setString("province", fuwuzhan.getProvince() + "%");
                        if (!StringUtils.isBlank(fuwuzhan.getCity()) && (!"请选择".equals(fuwuzhan.getCity())))
                        {
                            query.setString("city", fuwuzhan.getCity().replace("市", "") + "%");
                            if (!StringUtils.isBlank(fuwuzhan.getTown()) && (!"请选择".equals(fuwuzhan.getTown())))
                            {
                                query.setString("town", fuwuzhan.getTown() + "%");
                            }
                        }
                    }
                    query.setLong("fuwuzhanId2", fuwuzhanId);
                    if (!StringUtils.isBlank(sdate))
                    {
                        query.setString("sdate2", sdate);
                    }
                    if (!StringUtils.isBlank(edate))
                    {
                        query.setString("edate2", edate);
                    }
                    if (!StringUtils.isBlank(changeType))
                    {
                        query.setString("changeType2", changeType);
                    }
                    if (!StringUtils.isBlank(name))
                    {
                        query.setString("name2", "%" + name + "%");
                    }
                }
                List<StatisMendian> list2 = query.list();
                if (null != list2 && list2.size() > 0)
                {
                    list2.addAll(list);
                    json.setResult(list2);
                }
                else
                {
                    json.setResult(list);
                }
            }
        }
        return json;
    }

    @Override
    public HSSFWorkbook exportExcelFwz(Long fuwuzhanId , String sdate , String edate , String name , String changeType)
    {
        HSSFWorkbook book = null;
        String sheetname = sdate + "~" + edate;
        sdate = getFormatDate(sdate, true);
        edate = getFormatDate(edate, false);
        if (null != fuwuzhanId)
        {
            Session session = getCurrentSession();
            FuwuZhan fuwuzhan = (FuwuZhan) session.get(FuwuZhan.class, fuwuzhanId);
            if (null != fuwuzhan)
            {
                if (!StringUtils.isBlank(fuwuzhan.getName()))
                {
                    sheetname += "_" + fuwuzhan.getName();
                }
                StringBuilder sql = new StringBuilder();
                StringBuilder totalsql = new StringBuilder();
                Query query = null;

                totalsql.append("SELECT sum(usNum) AS usNum,sum(totalNum) AS totalNum,SUM(sum0) AS sum0,  SUM(sum1) AS sum1,SUM(sum2) AS sum2,SUM(sum3) AS sum3,SUM(sum4) AS sum4,SUM(sum5) AS sum5,sum(sum_near) AS sum_near,sum(sum_shm) AS sum_shm,sum(sum_wwd) AS sum_wwd,NUll as mendianId,'总计' AS `name`,null as lxr,NULL as address  FROM(");
                sql.append("SELECT count(userId) AS usNum,sum(totalNum) AS totalNum,SUM(sum0) AS sum0,  SUM(sum1) AS sum1,SUM(sum2) AS sum2,SUM(sum3) AS sum3,SUM(sum4) AS sum4,SUM(sum5) AS sum5,sum(sum_near) AS sum_near,sum(sum_shm) AS sum_shm,sum(sum_wwd) AS sum_wwd,mendianId,`name`,lxr,address  FROM(");
                sql.append("SELECT userId,count(id) AS totalNum,mendianId,`name`, address,lxr,sum(state0) sum0,sum(state1) sum1,sum(state2) sum2,sum(state3) sum3,sum(state4) sum4,sum(state5) sum5,sum(fujin) sum_near,sum(shm) sum_shm,sum(wwd) sum_wwd  FROM V_FWZ_STATIS_01 ");
                sql.append(" WHERE fuwuzhanId=:fuwuzhanId ");
                if (!StringUtils.isBlank(sdate))
                {
                    sql.append(" and crateDate>=:sdate ");
                }
                if (!StringUtils.isBlank(edate))
                {
                    sql.append(" and crateDate<=:edate ");
                }
                if (!StringUtils.isBlank(changeType))
                {
                    sql.append(" and changeType=:changeType ");
                }

                if (!StringUtils.isBlank(name))
                {
                    sql.append(" and name like :name");
                }
                sql.append(" GROUP BY address,lxr,`name`,mendianId,userId ");

                if (StringUtils.isBlank(name))
                {
                    sql.append(" UNION SELECT userId,count(id) AS totalNum,mendianId,`name`, address,lxr,sum(state0) sum0,sum(state1) sum1,sum(state2) sum2,sum(state3) sum3,sum(state4) sum4,sum(state5) sum5,sum(fujin) sum_near,sum(shm) sum_shm,sum(wwd) sum_wwd  FROM V_FWZ_STATIS_02  ");
                    sql.append(" WHERE 1=1 ");
                    if (!StringUtils.isBlank(fuwuzhan.getProvince()) && (!"请选择".equals(fuwuzhan.getProvince())))
                    {
                        sql.append(" and  province like :province ");
                        if (!StringUtils.isBlank(fuwuzhan.getCity()) && (!"请选择".equals(fuwuzhan.getCity())))
                        {
                            sql.append(" and city like :city ");
                            if (!StringUtils.isBlank(fuwuzhan.getTown()) && (!"请选择".equals(fuwuzhan.getTown())))
                            {
                                sql.append(" and town like :town ");
                            }
                        }
                    }
                    if (!StringUtils.isBlank(sdate))
                    {
                        sql.append(" and crateDate>=:sdate2 ");
                    }
                    if (!StringUtils.isBlank(edate))
                    {
                        sql.append(" and crateDate<=:edate2 ");
                    }
                    if (!StringUtils.isBlank(name))
                    {
                        sql.append(" and name like :name2");
                    }
                    if (!StringUtils.isBlank(changeType))
                    {
                        sql.append(" and changeType=:changeType2 ");
                    }
                    sql.append(" GROUP BY address,lxr,`name`,mendianId,userId");
                    sql.append(" UNION  SELECT userId,count(id) AS totalNum,mendianId,`name`, address,lxr,sum(state0) sum0,sum(state1) sum1,sum(state2) sum2,sum(state3) sum3,sum(state4) sum4,sum(state5) sum5,sum(fujin) sum_near,sum(shm) sum_shm,sum(wwd) sum_wwd  FROM V_FWZ_STATIS_01_s1 ");

                    sql.append(" WHERE fuwuzhanId=:fuwuzhanId2 ");

                    sql.append(" GROUP BY address,lxr,`name`,mendianId,userId ");
                }

                sql.append(") r GROUP BY  address,lxr,`name`,mendianId    order  by mendianId");
                totalsql.append(sql + " ) too ");//总计的统计

                query = session.createSQLQuery(sql.toString()).addScalar("address", StringType.INSTANCE).addScalar("mendianId", StandardBasicTypes.LONG).addScalar("lxr", StandardBasicTypes.STRING).addScalar("name", StandardBasicTypes.STRING).addScalar("totalNum", StandardBasicTypes.INTEGER).addScalar("sum_near", StandardBasicTypes.INTEGER).addScalar("sum_shm", StandardBasicTypes.INTEGER).addScalar("sum_wwd", StandardBasicTypes.INTEGER).addScalar("sum0", StandardBasicTypes.INTEGER).addScalar("sum1",
                        StandardBasicTypes.INTEGER).addScalar("usNum", StandardBasicTypes.INTEGER).addScalar("sum2", StandardBasicTypes.INTEGER).addScalar("sum3", StandardBasicTypes.INTEGER).addScalar("sum4", StandardBasicTypes.INTEGER).addScalar("sum5", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(StatisMendian.class));

                query.setLong("fuwuzhanId", fuwuzhanId);
                if (!StringUtils.isBlank(sdate))
                {
                    query.setString("sdate", sdate);
                }
                if (!StringUtils.isBlank(edate))
                {
                    query.setString("edate", edate);
                }
                if (!StringUtils.isBlank(changeType))
                {
                    query.setString("changeType", changeType);
                }
                if (!StringUtils.isBlank(name))
                {
                    query.setString("name", "%" + name + "%");
                }
                if (StringUtils.isBlank(name))
                {
                    if (!StringUtils.isBlank(fuwuzhan.getProvince()) && (!"请选择".equals(fuwuzhan.getProvince())))
                    {
                        query.setString("province", fuwuzhan.getProvince() + "%");
                        if (!StringUtils.isBlank(fuwuzhan.getCity()) && (!"请选择".equals(fuwuzhan.getCity())))
                        {
                            query.setString("city", fuwuzhan.getCity().replace("市", "") + "%");
                            if (!StringUtils.isBlank(fuwuzhan.getTown()) && (!"请选择".equals(fuwuzhan.getTown())))
                            {
                                query.setString("town", fuwuzhan.getTown() + "%");
                            }
                        }
                    }
                    query.setLong("fuwuzhanId2", fuwuzhanId);
                    if (!StringUtils.isBlank(sdate))
                    {
                        query.setString("sdate2", sdate);
                    }
                    if (!StringUtils.isBlank(edate))
                    {
                        query.setString("edate2", edate);
                    }
                    if (!StringUtils.isBlank(changeType))
                    {
                        query.setString("changeType2", changeType);
                    }
                    if (!StringUtils.isBlank(name))
                    {
                        query.setString("name2", "%" + name + "%");
                    }
                }

                query.setMaxResults(BuzConst.MAX_RESULT);
                List<StatisMendian> list = query.list();

                if (null != list && list.size() > 0)
                {
                    query = session.createSQLQuery(totalsql.toString()).addScalar("address", StringType.INSTANCE).addScalar("mendianId", StandardBasicTypes.LONG).addScalar("lxr", StandardBasicTypes.STRING).addScalar("name", StandardBasicTypes.STRING).addScalar("totalNum", StandardBasicTypes.INTEGER).addScalar("sum_near", StandardBasicTypes.INTEGER).addScalar("sum_shm", StandardBasicTypes.INTEGER).addScalar("sum_wwd", StandardBasicTypes.INTEGER).addScalar("sum0", StandardBasicTypes.INTEGER).addScalar("sum1",
                            StandardBasicTypes.INTEGER).addScalar("usNum", StandardBasicTypes.INTEGER).addScalar("sum2", StandardBasicTypes.INTEGER).addScalar("sum3", StandardBasicTypes.INTEGER).addScalar("sum4", StandardBasicTypes.INTEGER).addScalar("sum5", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(StatisMendian.class));

                    query.setLong("fuwuzhanId", fuwuzhanId);
                    if (!StringUtils.isBlank(sdate))
                    {
                        query.setString("sdate", sdate);
                    }
                    if (!StringUtils.isBlank(edate))
                    {
                        query.setString("edate", edate);
                    }
                    if (!StringUtils.isBlank(changeType))
                    {
                        query.setString("changeType", changeType);
                    }

                    if (!StringUtils.isBlank(name))
                    {
                        query.setString("name", "%" + name + "%");
                    }
                    if (StringUtils.isBlank(name))
                    {
                        if (!StringUtils.isBlank(fuwuzhan.getProvince()) && (!"请选择".equals(fuwuzhan.getProvince())))
                        {
                            query.setString("province", fuwuzhan.getProvince() + "%");
                            if (!StringUtils.isBlank(fuwuzhan.getCity()) && (!"请选择".equals(fuwuzhan.getCity())))
                            {
                                query.setString("city", fuwuzhan.getCity().replace("市", "") + "%");
                                if (!StringUtils.isBlank(fuwuzhan.getTown()) && (!"请选择".equals(fuwuzhan.getTown())))
                                {
                                    query.setString("town", fuwuzhan.getTown() + "%");
                                }
                            }
                        }
                        query.setLong("fuwuzhanId2", fuwuzhanId);
                        if (!StringUtils.isBlank(sdate))
                        {
                            query.setString("sdate2", sdate);
                        }
                        if (!StringUtils.isBlank(edate))
                        {
                            query.setString("edate2", edate);
                        }
                        if (!StringUtils.isBlank(changeType))
                        {
                            query.setString("changeType2", changeType);
                        }
                        if (!StringUtils.isBlank(name))
                        {
                            query.setString("name2", "%" + name + "%");
                        }
                    }
                    List<StatisMendian> list2 = query.list();
                    if (null != list2 && list2.size() > 0)
                    {
                        list.addAll(list2);
                    }
                    // 要从对象中获取的属性名
                    String fieldNames[] = { "mendianId", "name", "usNum", "totalNum", "sum_near", "sum_shm", "sum0", "sum1", "sum2", "sum3", "sum4", "address", "lxr" };
                    String titleNames[] = { "门店编号", "门店名称", "用户数", "订单总数", "附件门店订单", "上门服务订单", "未处理订单", "完成订单", "超期订单", "指派订单", "作废订单", "门店地址", "联系人" };
                    try
                    {
                        book = ExcelUtil.writeToExcelFile(list, fieldNames, titleNames, 0, 0, "filename", sheetname, true, null, MsOfficeVersion.Office2003, null);
                    }
                    catch (Exception e)
                    {
                        e.printStackTrace();
                    }
                }
            }
        }
        return book;
    }

    @Override
    public JsonResult<StatisPlusMendian> queryPlusMendPage(Long fuwuZhanId , String sdate , String edate , String name , String orderBy , String orderType , int currentPage , int pageSize)
    {
        sdate = getFormatDate(sdate, true);
        edate = getFormatDate(edate, false);
        JsonResult<StatisPlusMendian> json = new JsonResult<StatisPlusMendian>();
        Session session = getCurrentSession();
        StringBuilder countsql = new StringBuilder();
        StringBuilder sql = new StringBuilder();

        StringBuilder totalsql = new StringBuilder();
        totalsql.append("select sum(totalNum) as totalNum ,sum(plusNum) as plusNum, null as fuwuzhanId,'总计' as name,'' as  address,'' as lxrTel,'' as lxr   from (");

        sql.append("SELECT r.totalNum,ifnull(rr.totalNum,0) as plusNum,r.fuwuzhanId,ifnull(r.`name`,'未指定服务站门店') as name,r.address,r.lxrTel,r.lxr  FROM(SELECT  COUNT(*) as totalNum,ifnull(f.id,'0') as fuwuzhanId,f.`name`,f.address,f.lxrTel,f.lxr  from t_mendian m LEFT JOIN t_fuwuzhan f ON m.fuwuzhanId=f.id ");
        countsql.append("SELECT count(*) as counNum  FROM(SELECT  COUNT(*) as totalNum,ifnull(f.id,'0') as fuwuzhanId,f.`name`,f.address,f.lxrTel,f.lxr  from t_mendian m LEFT JOIN t_fuwuzhan f ON m.fuwuzhanId=f.id ");
        
        sql.append("WHERE m.status=" + BuzConst.STATUS_YES );
        countsql.append("WHERE  m.status=" + BuzConst.STATUS_YES );
        
        if (null != fuwuZhanId)
        {
            sql.append(" and f.id=:fuwuZhanId");
            countsql.append(" and  f.id=:fuwuZhanId");
        }
        else if (!StringUtils.isBlank(name))
        {
            sql.append(" and   f.`name` LIKE :name");
            countsql.append(" and  f.`name` :name");
        }
        sql.append(" GROUP BY f.id,f.`name`,f.address,f.lxrTel,f.lxr) r  left join (SELECT  COUNT(*) as totalNum,ifnull(f.id,'0') as fuwuzhanId,f.`name`  from t_mendian m LEFT JOIN t_fuwuzhan f ON m.fuwuzhanId=f.id where m.status=" + BuzConst.STATUS_YES+" " );
        countsql.append(" GROUP BY f.id,f.`name`,f.address,f.lxrTel,f.lxr)  r left join (SELECT  COUNT(*) as totalNum,ifnull(f.id,'0') as fuwuzhanId,f.`name`  from t_mendian m LEFT JOIN t_fuwuzhan f ON m.fuwuzhanId=f.id where m.status=" + BuzConst.STATUS_YES +" ");

        if (!StringUtils.isBlank(name))
        {
            sql.append(" and f.`name` LIKE :name2");
            countsql.append(" and f.`name` :name2");
        }
        if (!StringUtils.isBlank(sdate))
        {
            sql.append(" and m.crateDate>= :sdate ");
            countsql.append(" and m.crateDate>= :sdate ");
        }
        if (!StringUtils.isBlank(edate))
        {
            sql.append(" and m.crateDate<= :edate ");
            countsql.append(" and m.crateDate<= :edate ");
        }
        sql.append(" GROUP BY f.id,f.`name`) rr On r.fuwuzhanId=rr.fuwuzhanId ");
        countsql.append(" GROUP BY f.id,f.`name`) rr On r.fuwuzhanId=rr.fuwuzhanId ");

        totalsql.append(sql+") as total ");
        if (!StringUtils.isBlank(orderBy))
        {
            sql.append(" order by " + orderBy);
            if (!StringUtils.isBlank(orderType))
            {
                sql.append("   " + orderType);
            }
        }
        Query query = session.createSQLQuery(sql.toString()).addScalar("fuwuzhanId", StandardBasicTypes.LONG).addScalar("address", StringType.INSTANCE).addScalar("lxrTel", StandardBasicTypes.STRING).addScalar("lxr", StandardBasicTypes.STRING).addScalar("name", StandardBasicTypes.STRING).addScalar("totalNum", StandardBasicTypes.INTEGER).addScalar("plusNum", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(StatisPlusMendian.class));
        if (null != fuwuZhanId)
        {
            query.setLong("fuwuZhanId", fuwuZhanId);
        }
        else if (!StringUtils.isBlank(name))
        {
            query.setString("name", "%" + name + "%");
            query.setString("name2", "%" + name + "%");
        }
        if (!StringUtils.isBlank(sdate))
        {
            query.setString("sdate", sdate);
        }
        if (!StringUtils.isBlank(edate))
        {
            query.setString("edate", edate);
        }
        if (pageSize > 0)
        {
            int from = (currentPage - 1) * pageSize;
            query.setFirstResult(from);
            query.setMaxResults(pageSize);

        }
        List<StatisPlusMendian> list = query.list();
        query = session.createSQLQuery(countsql.toString()).addScalar("counNum", StandardBasicTypes.INTEGER);
        if (null != fuwuZhanId)
        {
            query.setLong("fuwuZhanId", fuwuZhanId);
        }
        else if (!StringUtils.isBlank(name))
        {
            query.setString("name", "%" + name + "%");
            query.setString("name2", "%" + name + "%");
        }
        if (!StringUtils.isBlank(sdate))
        {
            query.setString("sdate", sdate);
        }
        if (!StringUtils.isBlank(edate))
        {
            query.setString("edate", edate);
        }
        int totalCount = (Integer) query.uniqueResult();
        json.setTotalSize(totalCount);
        json.setPageSize(pageSize);
        json.setResult(list);
        if (currentPage < 2)
        {
            query = session.createSQLQuery(totalsql.toString()).addScalar("fuwuzhanId", StandardBasicTypes.LONG).addScalar("address", StringType.INSTANCE).addScalar("lxrTel", StandardBasicTypes.STRING).addScalar("lxr", StandardBasicTypes.STRING).addScalar("name", StandardBasicTypes.STRING).addScalar("totalNum", StandardBasicTypes.INTEGER).addScalar("plusNum", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(StatisPlusMendian.class));
            if (null != fuwuZhanId)
            {
                query.setLong("fuwuZhanId", fuwuZhanId);
            }
            else if (!StringUtils.isBlank(name))
            {
                query.setString("name", "%" + name + "%");
                query.setString("name2", "%" + name + "%");
            }
            if (!StringUtils.isBlank(sdate))
            {
                query.setString("sdate", sdate);
            }
            if (!StringUtils.isBlank(edate))
            {
                query.setString("edate", edate);
            }
            List<StatisPlusMendian> ttlist = query.list();
            if (null != ttlist && ttlist.size() > 0)
            {
                ttlist.addAll(list);
                json.setResult(ttlist);
            }
        }

        return json;
    }
}